<?
/*****************************************************************************************/
//■■トラッキングデータ収集
//
//■収集データ内容
//・経由検索エンジンデータ
//・検索クエリデータ
//・ユーザーアクセス動向
//
//■使い方
//redirect.php?Crt_Id={TB:creative::Crt_Id}をdistinationURLと置き換えて使用。
/*****************************************************************************************/

include_once("../Dao/db.php");
$diff_period = 0;

#クリエイティブID
$Crt_Id = $_GET['Crt_Id'];	
$Key_Id = $_GET['key_Id'];
//$Crt_Id=1;

if((!isset($Crt_Id) || $Crt_Id=='') && (!isset($Key_Id) || $Key_Id=='')){
	echo "タグ発行に関して、システム的な問題がございます。<br/>システム管理者に連絡ください。";
	exit;
}

$stamp = time();
$click_stamp = $stamp;
$clickDate = date('Y-m-d H:i:s', $click_stamp);
$clickDate2 = date('Y-m-d', $click_stamp);
$dayOfTheWeek = date("w",$stamp);

//第n週の判定
$Y = date('Y',$stamp);
$m = date('m',$stamp);
$d = date('d',$stamp);
$this_week = get_week($Y,$m,$d);

$weekly = $this_week[0];
//echo $weekly;


function get_week($yyyy, $mm, $dd) 
{
    $now_date = mktime(0,0,0,$mm,$dd,$yyyy);

    $w = (intval(date("w",$now_date)) + 6) % 7;
    
    $this_week[0] = date("Ymd",$now_date - 86400 * $w);
    $this_week[1] = date("Ymd",$now_date + 86400 * (6 - $w));
    
    return $this_week;
}

/*
$time = mktime(0,0,0,$m,1,$Y);
$wday = date("w",$time);
$weekly = (int)(($m + $wday - 1) / 7);
*/

$date = $clickDate;	



if($Crt_Id!='' && is_numeric($Crt_Id)){		
	//広告グループIDから登録に必須情報を取得
	$sql = "SELECT * FROM creative WHERE Crt_Id=$Crt_Id";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	//echo $sql;
	if(mysql_num_rows($result)==0){
		echo "システム上の問題の為、リンクが機能しておりません。<br/>Adlistingシステム担当者にご連絡ください。";
		exit;	
	}
	
	////echo $sql;
	$camp_ID = $row['camp_Id'];
	$adGr_ID = $row['adGr_Id'];		
	$MemberID = $row['memberId'];
	$listingType =  $row['listingType'];
	if($SearchEngineId==''){
		if($listingType==1)
			$SearchEngineId = 1;
		else if($listingType==2)
			$SearchEngineId = 2;
	}

	$destinationUrl_crt = $row['destinationUrl'];
	

	
	
	//print_r($row);
}else if($Key_Id!='' && is_numeric($Key_Id)){	
	//KeyIDから登録に必須情報を取得
	$sql = "SELECT * FROM keyword WHERE Key_Id=$Key_Id";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	//echo $sql;
	$camp_ID = $row['camp_Id'];
	$adGr_ID = $row['adGr_Id'];		
	$MemberID = $row['memberId'];
	$listingType =  $row['listingType'];
	if($SearchEngineId==''){
		if($listingType==1)
			$SearchEngineId = 1;
		else if($listingType==2)
			$SearchEngineId = 2;
	}

	$keyword = $row['text'];
	$type = $row['type'];
	$destinationUrl_key = $row['destinationUrl'];


	$Crt_Id = 0;
}else{
	//echo "タグ発行に関して、システム的な問題がございます。<br/>システム管理者に連絡ください。";
	exit;
}


/*
if($adGr_ID!=''){
	$sql = "SELECT countType,landingURL FROM adgroup WHERE adGr_Id=$adGr_ID";
	$result2 = mysql_query($sql);
	if(mysql_num_rows($result2)>0){
		$row2 = mysql_fetch_array($result2);
		
		
		$countType = $row2['countType'];
		$landingURL = $row2['landingURL'];
	}else{
		$countType = -1;
	}
}else{
	$countType = -1;	
}
*/	
	

//キーワード単位計測
if($destinationUrl_key!='')
	$destinationUrl = $destinationUrl_key;		
else if($destinationUrl_crt!='')
//クリエイティブ単位計測
	$destinationUrl = $destinationUrl_crt;



if($destinationUrl==''){

/*
		$sql = "SELECT landingURL AS landingURL FROM adgroup WHERE adGr_Id = $adGr_Id AND landingURL IS NOT NULL";
		$sql .= " UNION ";
		$sql .= "SELECT destinationUrl AS landingURL FROM creative WHERE adGr_Id = $adGr_Id AND destinationUrl IS NOT NULL";
		echo $sql;
		$result2 = mysql_query($sql);
		$row2 = mysql_fetch_array($result2);
		//print_r($row2);
		$destinationUrl = $row2['landingURL'];
*/		
	echo "クリエイティブID(Crt_Id)、キーワードID(Key_Id)に該当するランディング先URLがAdlistingシステム内に存在しません。<br/>";
	echo "Adwordを直接操作した場合、データの同期が取れない状態になる可能性があります。<br/>Adlisting管理者による同期の必要がございますので、管理者までお問い合わせください。";
	exit;
}



$tmpUrl = preg_match("/https?:\/\//",$destinationUrl) ? $destinationUrl : "http://".$destinationUrl;
//echo "du:".$destinationUrl;
$purl = parse_url($tmpUrl);
//print_r($purl);
$host = $purl['host'];
#HTTP REFERER
$referer = $_SERVER['HTTP_REFERER'];
//echo "<h1>Referer:$referer</h1>";

//Adwords仕様変更の為、取得できない場合あり、その場合は、
//Crt_Id、Key_Idに紐づくデータを使用。
//テストデータ
//$referer = "http://www.google.co.jp/search?q=%E3%82%AD%E3%83%BC%E3%83%AF%E3%83%BC%E3%83%89&sourceid=navclient-ff&ie=UTF-8&rlz=1B3GGGL_jaJP289JP289";



#Refererを解析
if($referer!=''){

	if(preg_match("/google/i",$referer,$matche)){
		#google経由の場合
		if(preg_match("/google\.\S+?\/\S*[&|\?]q=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 1;
		//echo "google経由ぽい";
	}else if(preg_match("/yahoo/i",$referer,$matche)){
		#yahoo経由の場合
		if(preg_match("/yahoo\.\S+?\/\S*[&|\?]p=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 2;
		//echo "yahoo経由ぽい";
	}else if(preg_match("/livedoor\.com/i",$referer,$matche)){
		#livedoor経由の場合
		if(preg_match("/livedoor\.com\/search\/\S*[&|\?]q=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 3;
		//echo "livedoor経由ぽい";
	}else if(preg_match("/goo\.ne\.jp/i",$referer,$matche)){
		#goo経由の場合
		if(preg_match("/goo\.ne\.jp\/\S*[&|\?]MT=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 4;
		//echo "goo経由ぽい";		
	}else if(preg_match("/nifty\.com/i",$referer,$matche)){
		#nifty経由の場合
		if(preg_match("/nifty\.com\/websearch\/\S*[&|\?]q=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 5;
		//echo "nifty経由ぽい";
	}else if(preg_match("/excite\.co\.jp/i",$referer,$matche)){
		#excite経由の場合
		if(preg_match("/excite\.co\.jp\/\S*[&|\?]search=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 6;
		//echo "excite経由ぽい";
	}else if(preg_match("/biglobe\.ne\.jp/i",$referer,$matche)){
		#biglobe経由の場合
		if(preg_match("/biglobe\.ne\.jp\/cgi-bin\/s\S*[&|\?]q=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 7;
		//echo "biglobe経由ぽい";
	}else if(preg_match("/infoseek\.co\.jp/i",$referer,$matche)){
		#infoseek経由の場合
		if(preg_match("/infoseek\.co\.jp\/\S*[&|\?]qt=([^&]+)/i",$referer,$matche2))$keyword = urldecode($matche2[1]);
		$SearchEngineId = 8;
		//echo "infoseek経由ぽい";
	}else if(preg_match("/msn\.co\.jp/i",$referer,$matche)){
		#msn経由の場合
		if(preg_match("/msn\.co\.jp\/\S*[&|\?]q=([^&]+)/i",$referer,$matche2))$keyword = urldecode($matche2[1]);
		$SearchEngineId = 9;
		//echo "msn経由ぽい";
	}else if(preg_match("/ask\.jp/i",$referer,$matche)){
		#ask.jp経由の場合
		if(preg_match("/ask\.jp\/\S*[&|\?]q=([^&]+)/i",$referer,$matche2))$keyword = urldecode($matche2[1]);
		$SearchEngineId = 10;
		//echo "ask.jp経由ぽい";
	}else if(preg_match("/aol\.com/i",$referer,$matche)){
		#aol経由の場合
		if(preg_match("/aol\.com\/\S*[&|\?]query=([^&]+)/i",$referer,$matche2)) $keyword = urldecode($matche2[1]);
		$SearchEngineId = 12;
		//echo "aol経由ぽい";
	}else if(preg_match("/fresheye\.com/i",$referer,$matche)){
		#fresheye経由の場合
		if(preg_match("/fresheye\.com\/\S*[&|\?]kw=([^&]+)/i",$referer,$matche2))$keyword = urldecode($matche2[1]);
		$SearchEngineId = 15;
		//echo "fresheye経由ぽい";
	}
	
	
//echo "クエリデータ:".$keyword."<br/>";
}



/**********************************************************************************************************/
//クエリデータ収集(ここから)
/**********************************************************************************************************/

//Referer経由のクエリデータが取得不能の場合、
//Key_Idからキーワードデータ、一致範囲を取得

if($referer!='') $keyword = urldecode($keyword);
if($referer!='') $keyword = mb_convert_encoding($keyword,"UTF-8","auto");

if($keyword != ''){


	$key_ok = 0;	
	if($referer!=''){
		//キーワードとキーワードIDのマッチング
		//全角スペースを半角スペースに変換
		$keyword = mb_ereg_replace("　", " ", $keyword);		
		//スペースをまとめる
		$keyword = preg_replace('!\s+!', " ", $keyword);	
		$tmp2 = explode(" ", $keyword);
		
		$sql2 = "SELECT key_Id, type,text FROM keyword WHERE MemberID=$MemberID AND adGr_ID=$adGr_ID AND listingType=$listingType ORDER BY type desc";
		////echo $sql2;
	
		$result2 = mysql_query($sql2); 
		while($row2 = mysql_fetch_array($result2)){
			$type = $row2['type'];
			$text = $row2['text'];
			$key_Id_t = $row2['key_Id'];
			//echo "Key_Id:$key_Id_t";
			//全角スペースを半角スペースに変換
			$text = mb_ereg_replace("　", " ", $text);		
			//スペースをまとめる
			$text = trim(preg_replace('!\s+!', " ", $text));
			$tmp3 = explode(" ",$text);
			//print_r($tmp2);
			
			if(count($tmp3) >= count($tmp2)){
			//echo "type:$type";
				//type:2部分一致 1全部一致
				switch($type){
					case 0:
						for($j=0;$j<count($tmp2);$j++){
							for($k=0;$k<count($tmp3);$k++){
								//echo "tmp2:".$tmp2[$j]."tmp3:".$tmp3[$k]."mb_strpos".mb_strpos($tmp3[$k],$tmp2[$j])."<br/>";
								if(mb_strpos($tmp3[$k],$tmp2[$j])!==FALSE){
									$key_ok = 1;
									break;
								}
							}
							if($key_ok==1) break;											
						}
					break;					
					case 1:
						if($text==$keyword)$key_ok = 1;						
					break;
					/*
					case 2:
						if(strstr($text, $keyword)!=FALSE)$key_ok = 1;
					break;
					*/
					case 2:
						for($j=0;$j<count($tmp2);$j++){
							for($k=0;$k<count($tmp3);$k++){
								//echo "tmp2:".$tmp2[$j]."tmp3:".$tmp3[$k]."mb_strpos".mb_strpos($tmp3[$k],$tmp2[$j])."<br/>";
								if(mb_strpos($tmp3[$k],$tmp2[$j])!==FALSE){
									$key_ok = 1;
									break;
								}
							}
							if($key_ok==1) break;											
						}
					break;			
				}				
			}
			if($key_ok == 1) break;			
		}//while
	}else{
		


	}	

	if($key_ok == 1){
		$key_Id = $key_Id_t;
	}


	
	//echo "Key_Id:$key_Id";
	//クエリデータ収集
	$sql = "SELECT clickCnt FROM queryreport 
			WHERE campaign_id=$camp_ID 
			AND adgroup_id=$adGr_ID AND MemberID=$MemberID 
			AND listingType=$listingType AND date = '$clickDate2' AND query = '$keyword'";
	////echo $sql;
	$result = mysql_query($sql);
	$query_cnt = mysql_num_rows($result);
	
	if($query_cnt > 0){
		$row = mysql_fetch_array($result);
		$clickCnt = $row['clickCnt']+1;
		$sql = "UPDATE queryreport 
				SET clickCnt=$clickCnt
				WHERE campaign_id=$camp_ID 
				AND adgroup_id=$adGr_ID AND MemberID=$MemberID 
				AND listingType=$listingType AND date = '$clickDate2' AND query = '$keyword'";
		$result = mysql_query($sql);
		////echo $sql;				
	}else{
		$clickCnt = 1;
		if($key_ok==1){
			$sql = "INSERT INTO queryreport(date,campaign_id,adgroup_id,MemberID,listingType,Crt_Id,keyword_id,clickCnt,query,weekly,dayOfTheWeek) 
					VALUES('$clickDate2',$camp_ID,$adGr_ID,$MemberID,$listingType,$Crt_Id,$key_Id,$clickCnt,'$keyword',$weekly,$dayOfTheWeek)";
			$result = mysql_query($sql);
			////echo $sql;		
		}else{
			$sql = "INSERT INTO queryreport(date,campaign_id,adgroup_id,MemberID,listingType,Crt_Id,clickCnt,query,weekly,dayOfTheWeek) 
					VALUES('$clickDate2',$camp_ID,$adGr_ID,$MemberID,$listingType,$Crt_Id,$clickCnt,'$keyword',$weekly,$dayOfTheWeek)";
			$result = mysql_query($sql);
			////echo $sql;					
		}			
	}			

}	


/**********************************************************************************************************/
//クエリデータ収集(ここまで)
/**********************************************************************************************************/


/**********************************************************************************************************/
//経由検索エンジン収集(ここから)
/**********************************************************************************************************/
$Key_Id = $Key_Id=="" ? 0 : $Key_Id;
if($SearchEngineId!=''){
	//経由検索エンジンデータ収集
	$sql = "SELECT clickCnt FROM searchenginereport 
			WHERE MemberID=$MemberID AND listingType=$listingType AND 
			campaign_id=$camp_ID AND adgroup_id=$adGr_ID AND 
			 SearchEngine_Id=$SearchEngineId AND date='$clickDate2' AND keyword_id=$Key_Id";
	////echo $sql;
	$result = mysql_query($sql);
	$query_cnt = mysql_num_rows($result);
	
	if($query_cnt > 0){
		$row = mysql_fetch_array($result);
		$clickCnt = $row['clickCnt']+1;
		$sql = "UPDATE searchenginereport 
				SET clickCnt=$clickCnt
				WHERE MemberID=$MemberID AND listingType=$listingType AND 
				campaign_id=$camp_ID AND adgroup_id=$adGr_ID AND 
				 SearchEngine_Id=$SearchEngineId AND date='$clickDate2' AND keyword_id=$Key_Id";
		$result = mysql_query($sql);
		////echo $sql;				
	}else{
		$clickCnt = 1;
		
		
		$i_w .= $key_ok==1 ? "keyword_id," : "";
		$v_w .= $key_ok==1 ? "$key_Id," : ""; 
		
		$sql = "INSERT INTO searchenginereport($i_w date,MemberID,Crt_Id,listingType,campaign_id,adgroup_id,SearchEngine_Id,weekly,dayOfTheWeek,clickCnt,keyword_id) 
				VALUES($v_w '$date',$MemberID,$Crt_Id,$listingType,$camp_ID,$adGr_ID,$SearchEngineId,$weekly,$dayOfTheWeek,$clickCnt,$Key_Id)";
		$result = mysql_query($sql);
		////echo $sql;					
					
	}		
}
/**********************************************************************************************************/
//経由検索エンジン収集(ここまで)
/**********************************************************************************************************/




if(is_ipadr($_SERVER['REMOTE_ADDR'])!==FALSE){
	#IP
	$ip = $_SERVER['REMOTE_ADDR'];
}		

//echo "<h1>IP:$ip</h1>";
#UID
//$uid = "";
		
#Click Date

//$click_date = date('Y-m-d H:m:d', $stamp);	

//
$sql = "SELECT Crt_Id,Key_Id FROM clicktracking 
		WHERE ip='$ip' AND camp_ID=$camp_ID 
		AND adGr_ID=$adGr_ID AND MemberID=$MemberID 
	    AND listingType=$listingType";
////echo $sql;
$result = mysql_query($sql);
$track_cnt = mysql_num_rows($result);



if($track_cnt==0){
	
	#初めてのリスティング経由での訪問　(=直接)
	// 広告グループID,IP,Keyword,Referer,URL,クリック日時,直接,間接,アシスト,ポスト効果
	//echo "#初めてのリスティング経由での訪問　(=直接)<br/>";

	$i_w .= $key_ok==1 ? "key_Id," : "";
	$v_w .= $key_ok==1 ? "$key_Id," : ""; 	
	$sql = "INSERT INTO clicktracking(
			$i_w clickDate,ip,camp_ID,adGr_ID,MemberID,
			listingType,Crt_Id,postCvFlag,
			directCvFlag,indirectCvFlag,asistCvFlag,host
			) 
			VALUES(
			$v_w '$clickDate','$ip',$camp_ID,$adGr_ID,$MemberID,
			$listingType,$Crt_Id,0,1,0,0,'$host')";
	$result = mysql_query($sql);
	////echo $sql;					
	
}else{

	$row = mysql_fetch_array($result);			
	//２回目以降の同一リスティング広告経由での訪問(=ポスト効果)
	//echo "２回目以降の同一リスティング広告経由での訪問(=ポスト効果)<br/>";

	if($Crt_Id == $row['Crt_Id']){
		//間接
		$set_words = "directCvFlag=0 ,indirectCvFlag=1, asistCvFlag=0 ,postCvFlag=1 ,clickDate='$clickDate'";
	}else{
		//アシスト
		$set_words = "directCvFlag=0 ,indirectCvFlag=0, asistCvFlag=1 ,postCvFlag=1 ,clickDate='$clickDate'";
	}
		
	$sql = "UPDATE clicktracking 
			SET  $set_words
			WHERE ip='$ip' AND camp_ID=$camp_ID 
			AND adGr_ID=$adGr_ID AND MemberID=$MemberID 
			AND Crt_ID=$Crt_Id AND listingType=$listingType";
	$result = mysql_query($sql);
	////echo $sql;					
}

$destinationUrl = preg_match("/https?:\/\//",$destinationUrl) ? $destinationUrl : "http://".$destinationUrl;
//echo "<h1>リダイレクト先:$destinationUrl</h1>";
header("Location: $destinationUrl");

function is_ipadr($str) {
    $pat = "/^([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})$/";
     //IPアドレスを表す正規表現
     preg_match($pat, $str, $regs);
 
     //定義域チェック (0 ≦ IP ≦ 255）
     for ($i = 1; $i <= 4; $i++) {
         if (!isset($regs[$i]))  return FALSE;
         if ($regs[$i] < 0 || $regs[$i] > 255)   return FALSE;
     }
 
     return $regs[0];
 }	


?>
